package org.hongzhisoft.dis.dao;

import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.hongzhisoft.dis.bean.User;
import org.hongzhisoft.dis.util.DataSourceTools;

/**
 * 用户数据持久处理类
 * 
 * @author HongChengzhi
 *
 */
public class UserDao {

    private DataSource dataSource = DataSourceTools.getDataSource();

    private final static String SELECT_ALL =
            "select id, account, password, remark, is_delete as 'isDelete' from t_user where is_delete=0";
    private final static String SELECT_BY_ID =
            "select id, account, password, remark, is_delete as 'isDelete' from t_user where id = ?";
    private final static String INSERT_SQL =
            "insert into t_user(account,password,remark) value(?,?,?)";
    private final static String UPDATE_SQL =
            "update t_user set password = ?,remark = ? where id = ?";
    private final static String DELETE_SQL =
            "update t_user set is_delete=1 where id = ?";
    private final static String LOGIN_SQL =
            "select id, account, password, remark, is_delete as 'isDelete' from t_user where account = ? and password = ? and is_delete=0";

    
    public List<User> selectAll() throws SQLException {

        QueryRunner queryRunner = new QueryRunner(dataSource);

        List<User> users = queryRunner.query(SELECT_ALL,
                new BeanListHandler<User>(User.class));

        return users;
    }

    public User selectById(int id) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(dataSource);

        User user = queryRunner.query(SELECT_BY_ID,
                new BeanHandler<User>(User.class), id);

        return user;

    }

    public int insertUser(User user) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(dataSource);
        return queryRunner.update(INSERT_SQL, user.getAccount(),
                user.getPassword(), user.getRemark());
    }

    public int updateUser(User user) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(dataSource);
        int result = queryRunner.update(UPDATE_SQL, user.getPassword(),
                user.getRemark(), user.getId());
        return result;
    }

    public int deleteUser(int id) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(dataSource);
        int result = queryRunner.update(DELETE_SQL, id);

        return result;
    }
    
    public User loginUser(String account, String password) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(dataSource);
         User result = queryRunner.query(LOGIN_SQL, new BeanHandler<User>(User.class), account, password);
        
        return result;
    }
    
    
}
